﻿using System;
using System.Collections.Generic;
using System.Text;
using XiaoGang.Soft.XGWMS.COM;

namespace XiaoGang.Soft.XGWMS.DAL_SQL
{
    public class DAL_M003_USER_SQL
    {
        #region SQL

        #region Get User Info

        private static string _getUserInfo_Sql = @"SELECT
     USER_ID , 
     USER_LOGIN_ID , 
     USER_WD , 
     USER_NAME , 
     USER_SEX , 
     USER_BIRTHDAY , 
     USER_PHONE1 , 
     USER_PHONE2 , 
     USER_ADDRESS1 , 
     USER_ADDRESS2 , 
     USER_EMAIL , 
     USER_POST , 
     USER_PHOTO , 
     USER_GROUP , 
     USER_STATE , 
     MEMO 
FROM 
     M003_USER 
WHERE USER_LOGIN_ID = @USER_LOGIN_ID
    AND TRANS_STATE <> '3'";

        #endregion

        #region Get User List

        private static string _getUserList_Sql = @"SELECT
    M003.USER_ID , 
    M003.USER_LOGIN_ID , 
    M003.USER_WD , 
    M003.USER_NAME , 
    M003.USER_SEX AS USER_SEX_HIDE, 
    (CASE M003.USER_SEX WHEN '0' THEN '男' ELSE '女' END) AS USER_SEX, 
    M003.USER_BIRTHDAY , 
    M003.USER_PHONE1 , 
    M003.USER_PHONE2 , 
    M003.USER_ADDRESS1 , 
    M003.USER_ADDRESS2 , 
    M003.USER_EMAIL , 
    M003.USER_POST , 
    M003.USER_PHOTO , 
    M003.USER_GROUP , 
    M002.GROUP_NM,
    M003.USER_STATE AS USER_STATE_HIDE,
    (CASE M003.USER_STATE WHEN '0' THEN '許可' ELSE '禁止' END) AS USER_STATE, 
    M003.MEMO 
FROM 
    M003_USER M003 LEFT JOIN M002_USER_GROUP M002
        ON M003.USER_GROUP = M002.GROUP_ID
WHERE 1=1 ";

        #endregion

        #region Add User

        private static string _addUser_Sql = @"INSERT INTO 
M003_USER (CREATE_DATETIME,
    UPDATE_DATETIME,
    TRANS_STATE,
    UPDATER_ID , 
    TERMINAL_CD , 
    USER_ID , 
    USER_LOGIN_ID , 
    USER_WD , 
    USER_NAME , 
    USER_SEX , 
    USER_BIRTHDAY , 
    USER_PHONE1 , 
    USER_PHONE2 , 
    USER_ADDRESS1 , 
    USER_ADDRESS2 , 
    USER_EMAIL , 
    USER_POST , 
    USER_PHOTO , 
    USER_GROUP , 
    USER_STATE , 
    MEMO ) 
VALUES ( " + CONST.CurrentTime + "," + CONST.CurrentTime + ",'1',@UPDATER_ID , @TERMINAL_CD , @USER_ID , @USER_LOGIN_ID , @USER_WD , @USER_NAME , @USER_SEX , @USER_BIRTHDAY , @USER_PHONE1 , @USER_PHONE2 , @USER_ADDRESS1 , @USER_ADDRESS2 , @USER_EMAIL , @USER_POST , @USER_PHOTO , @USER_GROUP , @USER_STATE , @MEMO ) ";

        #endregion

        #region get User Photo

        private static string _getUserPhoto_Sql = @"SELECT USER_PHOTO FROM M003_USER WHERE TRANS_STATE <> '3' AND USER_ID = @USER_ID";

        #endregion

        #region Delete User

        private static string _delUser_Sql = @"DELETE FROM M003_USER WHERE USER_ID = @USER_ID";

        #endregion

        #region Update User

        private static string _updateUser_Sql = @"UPDATE 
     M003_USER 
SET
     [UPDATE_DATETIME] = CONVERT(VARCHAR,GETDATE(),112)+REPLACE(CONVERT(VARCHAR,GETDATE(),108),':',''), 
     [TRANS_STATE] = '2' , 
     [UPDATER_ID] = @UPDATER_ID , 
     [TERMINAL_CD] = @TERMINAL_CD , 
     [USER_LOGIN_ID] = @USER_LOGIN_ID , 
     [USER_WD] = @USER_WD , 
     [USER_NAME] = @USER_NAME , 
     [USER_SEX] = @USER_SEX , 
     [USER_BIRTHDAY] = @USER_BIRTHDAY , 
     [USER_PHONE1] = @USER_PHONE1 , 
     [USER_PHONE2] = @USER_PHONE2 , 
     [USER_ADDRESS1] = @USER_ADDRESS1 , 
     [USER_ADDRESS2] = @USER_ADDRESS2 , 
     [USER_EMAIL] = @USER_EMAIL , 
     [USER_POST] = @USER_POST , 
     [USER_PHOTO] = @USER_PHOTO , 
     [USER_GROUP] = @USER_GROUP , 
     [USER_STATE] = @USER_STATE , 
     [MEMO] = @MEMO 
WHERE 
     USER_ID=@USER_ID ";

        #endregion

        #region Check User Exist

        private static string _checkUserExist_Sql = @"SELECT USER_ID FROM M003_USER WHERE 1=1 ";

        #endregion

        #region Check User Used

        private static string _checkUserUsed_Sql = @"Select distinct UserID from (
Select distinct STOREROOM_MANAGER as UserID from M008_STOREROOM Where STOREROOM_MANAGER = @UserID
Union 
Select distinct UPDATER_ID as UserID from M001_BASE Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M002_USER_GROUP Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M003_USER Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M004_POWER Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M005_POWER_CONFIG Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M007_GOODS Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M008_STOREROOM Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M009_STORAGE Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M010_BILL Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M011_GET_ID Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M012_MEMBER Where UPDATER_ID = @UserID
Union 
Select distinct UPDATER_ID as UserID from M014_MerberBuy Where UPDATER_ID = @UserID
) A";

        #endregion

        #endregion

        #region Property

        /// <summary>
        /// GetUserInfo
        /// </summary>
        public static string GetUserInfo
        {
            get { return _getUserInfo_Sql; }
        }

        /// <summary>
        /// GetUserList
        /// </summary>
        public static string GetUserList
        {
            get { return _getUserList_Sql; }
        }

        /// <summary>
        /// AddUser
        /// </summary>
        public static string AddUser
        {
            get { return _addUser_Sql; }
        }

        /// <summary>
        /// GetUserPhoto
        /// </summary>
        public static string GetUserPhoto
        {
            get { return _getUserPhoto_Sql; }
        }

        /// <summary>
        /// DelUser
        /// </summary>
        public static string DelUser
        {
            get { return _delUser_Sql; }
        }

        /// <summary>
        /// UpdateUser
        /// </summary>
        public static string UpdateUser
        {
            get { return _updateUser_Sql; }
        }

        /// <summary>
        /// CheckUserExist
        /// </summary>
        public static string CheckUserExist
        {
            get { return _checkUserExist_Sql; }
        }

        /// <summary>
        /// 检查用户是否被使用
        /// </summary>
        public static string CheckUserUsed
        {
            get { return _checkUserUsed_Sql; }
        }

        #endregion
    }
}
